//
//  DatabaseManager.m
//  Voxper
//
//  Created by Erphan on 2/10/11.
//  Copyright 2011 Smonte. All rights reserved.
//

#import "DatabaseManager.h"
#import "SectionItem.h"
#import "Country.h"
#import "PhoneContact.h"
#import "ConversationItem.h"
#import "MessageItem.h"
#import "ABRecordReader.h"
#import "ABContact.h"
#import "ABContactsHelper.h"
#import "RecentCallItem.h"
#import "VoxperAppDelegate.h"
#import "UIManager.h"
#import "Settings.h"

#import <sqlite3.h>
#import <AddressBook/AddressBook.h>

#define DBNAME @"Voxper.sqlite"

@interface DatabaseManager(Private)

- (void)copyDatabaseIfNeeded;
- (NSString *)getDBPath;
- (int)getContactId:(NSString *)str_localId;
- (BOOL)markSipContact:(int)contactId bSipContact:(BOOL)bSipContact;

@end


@implementation DatabaseManager

- (id)init {
    self = [super init];
	if (self) {
		[self copyDatabaseIfNeeded];
	}
	return self;
}

- (void)dealloc {
	if (database) {
		sqlite3_close(database);
	}
	[super dealloc];
}


#pragma mark -
#pragma mark Private

- (void) copyDatabaseIfNeeded {
	NSFileManager *fileManager = [NSFileManager defaultManager];
	NSError *error;
	NSString *dbPath = [self getDBPath];
	BOOL success = [fileManager fileExistsAtPath:dbPath]; 
	
	if(!success) {
		
		NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:DBNAME];
		success = [fileManager copyItemAtPath:defaultDBPath toPath:dbPath error:&error];
		
		if (!success) 
			NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
	}
}

- (NSString *) getDBPath {
	NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory , NSUserDomainMask, YES);
	NSString *documentsDir = [paths objectAtIndex:0];
	return [documentsDir stringByAppendingPathComponent:DBNAME];
}

- (int)getContactId:(NSString *)str_localId {
	int returnValue = -1;
	NSString *dbPath = [self getDBPath];
	if (!database) {
		if(sqlite3_open([dbPath UTF8String], &database) != SQLITE_OK) {
			sqlite3_close(database);
			return returnValue;
		}
	}
	
	const char *sql = "select id from contacts where externalid = ?";
	sqlite3_stmt *selectstmt;
	if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) == SQLITE_OK) {
		sqlite3_bind_text(selectstmt, 1, [str_localId UTF8String], -1, SQLITE_TRANSIENT);
		
		while(sqlite3_step(selectstmt) == SQLITE_ROW) {
			returnValue = sqlite3_column_int(selectstmt, 0);
			break;
		}
	}
	if(selectstmt)
		sqlite3_finalize(selectstmt);
	
	return returnValue;
}

- (BOOL)markSipContact:(int)contactId bSipContact:(BOOL)bSipContact{
	NSString *dbPath = [self getDBPath];
	if (!database) {
		if(sqlite3_open([dbPath UTF8String], &database) != SQLITE_OK) {
			sqlite3_close(database);
			return NO;
		}
	}

	if (contactId < 0) {
		const char *sql = "update contacts set sipuser = 0";
		sqlite3_stmt *updatestmt;
		if(sqlite3_prepare_v2(database, sql, -1, &updatestmt, NULL) == SQLITE_OK) {		
			if(SQLITE_DONE != sqlite3_step(updatestmt))
				NSAssert1(0, @"Error while updating data. '%s'", sqlite3_errmsg(database));
			else {
				NSLog(@"All contacts are marked as non sip");
			}
		}
		if(updatestmt)
			sqlite3_finalize(updatestmt);

	} else {
		const char *sql = "update contacts set sipuser = ? where id = ?";
		sqlite3_stmt *updatestmt;
		if(sqlite3_prepare_v2(database, sql, -1, &updatestmt, NULL) == SQLITE_OK) {
			sqlite3_bind_int(updatestmt, 1, bSipContact?1:0);
			sqlite3_bind_int(updatestmt, 2, contactId);
			if(SQLITE_DONE != sqlite3_step(updatestmt))
				NSAssert1(0, @"Error while updating data of user. '%s'", sqlite3_errmsg(database));
			else {
				NSLog(@"1 contact's sip flag updated");
			}
		}
		if(updatestmt)
			sqlite3_finalize(updatestmt);

	}

	return YES;
}

#pragma mark -
#pragma mark Public

- (BOOL)getCountriesList:(NSMutableArray *)arrayList {
	if (!arrayList) {
		return NO;
	}
	
	NSString *dbPath = [self getDBPath];
	if (!database) {
		if(sqlite3_open([dbPath UTF8String], &database) != SQLITE_OK) {
			sqlite3_close(database);
			return NO;
		}
	}
	
	if (database) {
		for (int i = 65; i < 91; i++) {			
			SectionItem *sectionItem = [[SectionItem alloc] init];
			
			sectionItem.sectionName = [NSString stringWithFormat:@"%c", i];
			//NSLog(@"Section: %@", sectionItem.sectionName);
			
			NSString *tempSql = [NSString stringWithFormat:@"select name, iso2, dial_code from countries where name LIKE '%@%%' order by name", sectionItem.sectionName];
			const char *cQuery = [tempSql UTF8String];
			sqlite3_stmt *selectStatement;
			
			if (sqlite3_prepare_v2(database, cQuery, -1, &selectStatement, NULL) != SQLITE_OK) {
                [sectionItem release];
                return NO;
            }
			
			sectionItem.sectionItems = [[NSMutableArray alloc] init];
			
			while (sqlite3_step(selectStatement) == SQLITE_ROW) {
				Country *country = [[Country alloc] init];
				
				country.countryName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectStatement, 0)];
				country.countryCode = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectStatement, 1)];
				country.dialingCode = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectStatement, 2)];
				
				//NSLog(@"%@ - %@ - %@", country.countryName, country.countryName, country.dialingCode);
				
				[sectionItem.sectionItems addObject:country];
				[country release];
			}
			
			[arrayList addObject:sectionItem];
			[sectionItem release];
		}
	}

	
	return YES;
}

- (BOOL)getCountry:(Country *)country :(NSString *)condition {
	
	if (!country) {
		return NO;
	}
	
	NSString *dbPath = [self getDBPath];
	if (!database) {
		if(sqlite3_open([dbPath UTF8String], &database) != SQLITE_OK) {
			sqlite3_close(database);
			return NO;
		}
	}
	
	NSString *tempSql = [NSString stringWithFormat:@"select name, iso2, dial_code from countries where iso2 LIKE '%@%%' order by name", condition];
	const char *cQuery = [tempSql UTF8String];
	sqlite3_stmt *selectStatement;
	
	if (sqlite3_prepare_v2(database, cQuery, -1, &selectStatement, NULL) != SQLITE_OK)
		return NO;
		
	while (sqlite3_step(selectStatement) == SQLITE_ROW) {
		
		country.countryName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectStatement, 0)];
		country.countryCode = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectStatement, 1)];
		country.dialingCode = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectStatement, 2)];
		
		//NSLog(@"DBManager -> GetCountry: %@ - %@ - %@", country.countryName, country.countryName, country.dialingCode);
	}
	return YES;
}

- (BOOL)getContacts:(NSMutableArray *)arrayList bVoxperOnly:(BOOL)bVoxperOnly {
	if (!arrayList) {
		return NO;
	}
	
	NSString *dbPath = [self getDBPath];
	if (!database) {
		if(sqlite3_open([dbPath UTF8String], &database) != SQLITE_OK) {
			sqlite3_close(database);
			return NO;
		}
	}
	
	if (database) {
        for (int i = 65; i < 92; i++) {
            NSString *tempSql = @"";
            
			SectionItem *sectionItem = [[SectionItem alloc] init];
            if (i == 91) {
                sectionItem.sectionName = @"#";
                
                tempSql = @"select externalid, firstname, lastname, sipuser from contacts where firstname NOT between 'A%' and 'Z%' AND firstname NOT LIKE 'Z%' order by firstname, lastname";
                
                if (bVoxperOnly) {
                    tempSql = @"select externalid, firstname, lastname, sipuser from contacts where firstname LIKE '[A-z]%%' AND sipuser = 1 order by firstname, lastname";
                }
            } else {
                sectionItem.sectionName = [NSString stringWithFormat:@"%c", i];
                
                tempSql = [NSString stringWithFormat:@"select externalid, firstname, lastname, sipuser from contacts where firstname LIKE '%@%%' order by firstname, lastname", sectionItem.sectionName];
                
                if (bVoxperOnly) {
                    tempSql = [NSString stringWithFormat:@"select externalid, firstname, lastname, sipuser from contacts where firstname LIKE '%@%%' AND sipuser = 1 order by firstname, lastname", sectionItem.sectionName];
                }
            }
            
            const char *cQuery = [tempSql UTF8String];
            sqlite3_stmt *selectStatement;
            
            if (sqlite3_prepare_v2(database, cQuery, -1, &selectStatement, NULL) != SQLITE_OK) {
                [sectionItem release];
                return NO;
            }
            sectionItem.sectionItems = [[NSMutableArray alloc] init];
            
            while (sqlite3_step(selectStatement) == SQLITE_ROW) {
                PhoneContact *contact = [[PhoneContact alloc] init];
                
                contact.Id = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectStatement, 0)];
                
                char *firstName = (char *)sqlite3_column_text(selectStatement, 1);
                char *lastName = (char *)sqlite3_column_text(selectStatement, 2);
                
                if (firstName != nil && strlen(firstName) != 0) {
                    contact.FirstName = [NSString stringWithUTF8String:firstName];
                }
                
                if (lastName != nil && strlen(lastName) != 0) {
                    contact.LastName = [NSString stringWithUTF8String:lastName];
                }
                
                contact.IsSipUser = (sqlite3_column_type(selectStatement, 3) == SQLITE_NULL) ? 0 : sqlite3_column_int(selectStatement, 3);
                
                [sectionItem.sectionItems addObject:contact];
                [contact release];
            }
            
            if (!sectionItem.sectionItems || [sectionItem.sectionItems count] == 0) {
                [sectionItem release];
                continue;
            }
            
            [arrayList addObject:sectionItem];
            [sectionItem release];
		}
        
//		const char *sqlsections = "select substr(firstname, 1, 1) as initial from contacts group by initial order by initial";
//		sqlite3_stmt *selectsectionsstmt;
//		if(sqlite3_prepare_v2(database, sqlsections, -1, &selectsectionsstmt, NULL) == SQLITE_OK) {
//			while (sqlite3_step(selectsectionsstmt) == SQLITE_ROW) {
//				char *cSectionName = (char *)sqlite3_column_text(selectsectionsstmt, 0);
//				if(cSectionName == nil || strlen(cSectionName) == 0) {
//					NSLog(@"section name is nil or empty");
//					continue;
//				}
//				SectionItem *sectionItem = [[SectionItem alloc] init];
//				sectionItem.sectionName = [NSString stringWithUTF8String:cSectionName];
//				
//			}
//		}
	}
	
	
	return YES;
}

- (BOOL)getSipContacts:(NSString *)strLocalId destinationArray:(NSMutableArray *)destinationArray {
	int contactId = [self getContactId:strLocalId];
	
	if (contactId < 0) {
		return NO;
	}
	
	NSString *dbPath = [self getDBPath];
	if (!database) {
		if(sqlite3_open([dbPath UTF8String], &database) != SQLITE_OK) {
			sqlite3_close(database);
			return NO;
		}
	}
	
	NSString *tempSql = [NSString stringWithFormat:@"select phoneno from phonenumbers where contactid=%d", contactId];
	const char *cQuery = [tempSql UTF8String];
	sqlite3_stmt *selectStatement;
	
	if (sqlite3_prepare_v2(database, cQuery, -1, &selectStatement, NULL) != SQLITE_OK)
		return NO;
	
	while (sqlite3_step(selectStatement) == SQLITE_ROW) {
		[destinationArray addObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(selectStatement, 0)]];
	}

	return YES;
}

- (BOOL)addContact:(PhoneContact *)contact {
	if (!contact) {
		return NO;
	}
	
	NSString *dbPath = [self getDBPath];
	if (!database) {
		if(sqlite3_open([dbPath UTF8String], &database) != SQLITE_OK) {
			sqlite3_close(database);
			return NO;
		}
	}
	
	BOOL bFound = NO;
	const char *sql = "select firstName, lastName from contacts where externalid = ?";
	sqlite3_stmt *selectstmt;
	if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) == SQLITE_OK) {
		sqlite3_bind_text(selectstmt, 1, [contact.Id UTF8String], -1, SQLITE_TRANSIENT);
		
		while(sqlite3_step(selectstmt) == SQLITE_ROW) {
			bFound = YES;
			//FIXME: Update firstName, lastName if different
			break;
		}
	}
	if(selectstmt)
		sqlite3_finalize(selectstmt);
	
	if(bFound) {
		return NO;
	}
	
	sqlite3_stmt *addStatement;
	
	const char *sqlInsert = "insert into contacts(firstname, lastname, externalid, sipuser) Values(?, ?, ?, 0)";
	
	if(sqlite3_prepare_v2(database, sqlInsert, -1, &addStatement, NULL) != SQLITE_OK)
		NSAssert1(0, @"Error while creating add statement. '%s'", sqlite3_errmsg(database));
	
	sqlite3_bind_text(addStatement, 1, [contact.FirstName UTF8String], -1, SQLITE_TRANSIENT);
	if (contact.LastName) {
		sqlite3_bind_text(addStatement, 2, [contact.LastName UTF8String], -1, SQLITE_TRANSIENT);
	} else {
		sqlite3_bind_text(addStatement, 2, "", -1, SQLITE_TRANSIENT);
	}

	
	sqlite3_bind_text(addStatement, 3, [contact.Id UTF8String], -1, SQLITE_TRANSIENT);
	
	if(SQLITE_DONE != sqlite3_step(addStatement))
		NSAssert1(0, @"Error while inserting data. '%s'", sqlite3_errmsg(database));
	else {
		//NSLog(@"Contact Record inserted");
	}
	
	sqlite3_reset(addStatement);
	return YES;
}

- (BOOL)addSipPhoneNumbers:(NSString *)strLocalId strPhoneNumber:(NSString *)strPhoneNumber strSipNumber:(NSString *)strSipNumber {
	int contactId = [self getContactId:strLocalId];
	if (contactId < 0) {
		// ADD User
		NSLog(@"Add user to db as it doesn't exist in the db");
		return NO;
	}
	
	if (![self markSipContact:contactId bSipContact:YES])
		return NO;
	
	sqlite3_stmt *addStatement;
	
	const char *sqlInsert = "insert into phonenumbers(phoneno, sipno, contactid) Values(?, ?, ?)";
	
	if(sqlite3_prepare_v2(database, sqlInsert, -1, &addStatement, NULL) != SQLITE_OK)
		NSAssert1(0, @"Error while creating add statement. '%s'", sqlite3_errmsg(database));
	
	sqlite3_bind_text(addStatement, 1, [strPhoneNumber UTF8String], -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(addStatement, 2, [strSipNumber UTF8String], -1, SQLITE_TRANSIENT);
	sqlite3_bind_int(addStatement, 3, contactId);
	
	if(SQLITE_DONE != sqlite3_step(addStatement))
		NSAssert1(0, @"Error while inserting data. '%s'", sqlite3_errmsg(database));
	else {
		NSLog(@"Sip Phone number saved. Record inserted");
	}
	
	sqlite3_reset(addStatement);
	return YES;
}

- (BOOL)clearSipPhoneNumbers {
	sqlite3_stmt *deleteStatement;
	
	const char *sqlInsert = "delete from phonenumbers";
	
	if(sqlite3_prepare_v2(database, sqlInsert, -1, &deleteStatement, NULL) != SQLITE_OK)
		NSAssert1(0, @"Error while creating add statement. '%s'", sqlite3_errmsg(database));
	
	if(SQLITE_DONE != sqlite3_step(deleteStatement))
		NSAssert1(0, @"Error while inserting data. '%s'", sqlite3_errmsg(database));
	else {
		NSLog(@"Sip Phone number saved. Record inserted");
	}
	
	sqlite3_reset(deleteStatement);
	return YES;
}

- (BOOL)getConversations:(NSMutableArray *)arrayList :(NSString *)strMyNumber {
	if (!arrayList) {
		return NO;
	}
	
	[arrayList removeAllObjects];
	
	NSString *dbPath = [self getDBPath];
	if (!database) {
		if(sqlite3_open([dbPath UTF8String], &database) != SQLITE_OK) {
			sqlite3_close(database);
			return NO;
		}
	}
	
	if (database) {
		NSString *strQuery = [NSString stringWithFormat:@"SELECT max(id) as MAXID, * FROM (SELECT id, fromPhone, fromlocalid, textmessage, read, messagetime, remotemessageid, direction, tophone FROM messages UNION SELECT id, toPhone, fromlocalid, textmessage, read, messagetime, remotemessageid, direction, fromphone FROM messages) INNERTABLE WHERE fromPhone <> '%@' GROUP BY fromPhone, tophone ORDER BY messagetime desc", strMyNumber];
		const char *cQuery = [strQuery UTF8String];
		//const char *cQuery = "SELECT fromphone, fromlocalid, textmessage, read, messagetime, tophone, direction FROM messages group by fromphone order by id";
		sqlite3_stmt *selectStatement;
		
		if (sqlite3_prepare_v2(database, cQuery, -1, &selectStatement, NULL) != SQLITE_OK)
			return NO;
		
		ABAddressBookRef *addressBook = (ABAddressBookRef *) ABAddressBookCreate();
		while (sqlite3_step(selectStatement) == SQLITE_ROW) {
			ConversationItem *conversationItem = [[ConversationItem alloc] init];
			
			conversationItem.FromPhone = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectStatement, 2)];
			
            conversationItem.ContactId = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectStatement, 3)];
			ABRecordRef person = ABAddressBookGetPersonWithRecordID(addressBook, [conversationItem.ContactId intValue]);
			if (person) {
				conversationItem.FromName = [ABRecordReader personCompositeName:person];
			} else {
				conversationItem.FromName = @"";
			}

			conversationItem.LastTextMessage = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectStatement, 4)];
			conversationItem.HasUnreadItems = !((BOOL)sqlite3_column_int(selectStatement, 5));
			conversationItem.LastTextMessageTime = sqlite3_column_double(selectStatement, 6);//[[NSDate dateWithTimeIntervalSince1970:sqlite3_column_double(selectStatement, 6)] description];
			
			[arrayList addObject:conversationItem];

			[conversationItem release];
		}
		CFRelease(addressBook);
	}
	
	
	return YES;
}

- (BOOL)getConversation:(NSMutableArray *)arrayList :(NSString *)strNumber {
	if (!arrayList) {
		return NO;
	}
	
	[arrayList removeAllObjects];
	
	NSString *dbPath = [self getDBPath];
	if (!database) {
		if(sqlite3_open([dbPath UTF8String], &database) != SQLITE_OK) {
			sqlite3_close(database);
			return NO;
		}
	}
	
	if (database) {
		NSString *strQuery = [NSString stringWithFormat:@"SELECT fromPhone, fromlocalid, textmessage, messagetime, remotemessageid, direction, tophone FROM messages WHERE fromPhone == '%@' OR tophone == '%@' ORDER BY messagetime", strNumber, strNumber];
		const char *cQuery = [strQuery UTF8String];

		sqlite3_stmt *selectStatement;
		
		if (sqlite3_prepare_v2(database, cQuery, -1, &selectStatement, NULL) != SQLITE_OK)
			return NO;
		
		ABAddressBookRef *addressBook = (ABAddressBookRef *) ABAddressBookCreate();
		while (sqlite3_step(selectStatement) == SQLITE_ROW) {
			MessageItem *messageItem = [[MessageItem alloc] init];
			
			messageItem.FromPhone = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectStatement, 0)];
			
			messageItem.ContactId = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectStatement, 1)];
			ABRecordRef person = ABAddressBookGetPersonWithRecordID(addressBook, [messageItem.ContactId intValue]);
			if (person) {
				messageItem.ContactName = [ABRecordReader personCompositeName:person];
			} else {
				messageItem.ContactName = @"";
			}
			
			messageItem.Message = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectStatement, 2)];
			messageItem.Time = sqlite3_column_double(selectStatement, 3);
			
			[arrayList addObject:messageItem];
			
			[messageItem release];
		}
		CFRelease(addressBook);
	}
	
	
	return YES;
}

- (BOOL)deleteConversationFromNumber:(NSString *)strNumber {
    sqlite3_stmt *deleteStatement;
	
	const char *sqlInsert = [[NSString stringWithFormat:@"delete from messages where fromPhone = '%@' OR toPhone = '%@'", strNumber, strNumber] UTF8String];
	
	if(sqlite3_prepare_v2(database, sqlInsert, -1, &deleteStatement, NULL) != SQLITE_OK)
		NSAssert1(0, @"Error while creating add statement. '%s'", sqlite3_errmsg(database));
	
	if(SQLITE_DONE != sqlite3_step(deleteStatement))
		NSAssert1(0, @"Error while deleting data  '%s'", sqlite3_errmsg(database));
	else {
		NSLog(@"Sip Phone number saved. Record inserted");
	}
	
	sqlite3_reset(deleteStatement);
	return YES;
}

- (BOOL)markConversationAsRead:(NSString *)strNumber {
	NSString *dbPath = [self getDBPath];
	if (!database) {
		if(sqlite3_open([dbPath UTF8String], &database) != SQLITE_OK) {
			sqlite3_close(database);
			return NO;
		}
	}
	
	sqlite3_stmt *addStatement;

	NSString *strQuery = [NSString stringWithFormat:@"update messages set read = 1 where fromPhone = '%@' OR toPhone = '%@'", strNumber, strNumber];
	const char *sqlInsert = [strQuery UTF8String];

	if(sqlite3_prepare_v2(database, sqlInsert, -1, &addStatement, NULL) != SQLITE_OK)
		NSAssert1(0, @"Error while creating add statement. '%s'", sqlite3_errmsg(database));
	
	if(SQLITE_DONE != sqlite3_step(addStatement))
		NSAssert1(0, @"Error while inserting data. '%s'", sqlite3_errmsg(database));
	else {
		NSLog(@"Conversation Marked as read");
	}
	
	sqlite3_reset(addStatement);
	return YES;
	
}

- (BOOL)addTextMessage:(NSString *)fromPhone :(NSString *)message :(NSDate *)messageTime :(BOOL)bIsRead :(NSString *)remoteMessageId :(NSString *)tophone :(int)direction{

	NSString *dbPath = [self getDBPath];
	if (!database) {
		if(sqlite3_open([dbPath UTF8String], &database) != SQLITE_OK) {
			sqlite3_close(database);
			return NO;
		}
	}
	
	BOOL bFound = NO;
	const char *sql = "select remotemessageid from messages where remotemessageid = ?";
	sqlite3_stmt *selectstmt;
	if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) == SQLITE_OK) {
		sqlite3_bind_text(selectstmt, 1, [remoteMessageId UTF8String], -1, SQLITE_TRANSIENT);
		
		while(sqlite3_step(selectstmt) == SQLITE_ROW) {
			bFound = YES;
			break;
		}
	}
	if(selectstmt)
		sqlite3_finalize(selectstmt);
	
	if(bFound) {
		return NO;
	}
	
	sqlite3_stmt *addStatement;
	
	const char *sqlInsert = "insert into messages(fromPhone, textmessage, messagetime, remotemessageid, read, fromlocalid, tophone, direction) Values(?, ?, ?, ?, ?, ?, ?, ?)";
	
	if(sqlite3_prepare_v2(database, sqlInsert, -1, &addStatement, NULL) != SQLITE_OK)
		NSAssert1(0, @"Error while creating conversation add statement. '%s'", sqlite3_errmsg(database));
	
	sqlite3_bind_text(addStatement, 1, [fromPhone UTF8String], -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(addStatement, 2, [message UTF8String], -1, SQLITE_TRANSIENT);
	sqlite3_bind_double(addStatement, 3, [messageTime timeIntervalSince1970]);
	sqlite3_bind_text(addStatement, 4, [remoteMessageId UTF8String], -1, SQLITE_TRANSIENT);
	sqlite3_bind_int(addStatement, 5, bIsRead);
	sqlite3_bind_text(addStatement, 7, [tophone UTF8String], -1, SQLITE_TRANSIENT);
	sqlite3_bind_int(addStatement, 8, direction);
	
	//NSLog(@"FromPhone: %@, ToPhone: %@", fromPhone, tophone);
	
	NSString *localFromPhoneId = @"-1";
	NSString *strPhone = @"";
	
	ABAddressBookRef *addressBook = (ABAddressBookRef *) ABAddressBookCreate();
	
	CFArrayRef allPeople = ABAddressBookCopyArrayOfAllPeople(addressBook);
	CFIndex nPeople = ABAddressBookGetPersonCount(addressBook);
	
	if (direction == 1) {
		strPhone = tophone;
	}
	else {
		strPhone = fromPhone;
	}
	
	VoxperAppDelegate *app = (VoxperAppDelegate *)[UIApplication sharedApplication].delegate;
	
	if ([strPhone hasPrefix:[app.uiManager.settings getMyLocationCode]]) {
		strPhone = [strPhone substringFromIndex:[[app.uiManager.settings getMyLocationCode] length]];
	}
	
	BOOL bPhoneNumberFound = NO;
	for (int i = 0; i < nPeople; i++) {
		ABRecordRef person = CFArrayGetValueAtIndex(allPeople, i);
		if(person != nil){
			NSArray *personPhoneNumbers = [ABRecordReader personContactNumbers:person :YES];
			for (int j = 0; j < [personPhoneNumbers count]; j++) {
				NSString *strPhoneNumber = [personPhoneNumbers objectAtIndex:j];
				//NSLog(@"AddressBook: %@ - From: %@", strPhoneNumber, strPhone);
				if ([strPhoneNumber rangeOfString:strPhone].length > 0) {
					localFromPhoneId = [NSString stringWithFormat:@"%d", ABRecordGetRecordID(person)];
					bPhoneNumberFound = YES;
					break;
				}
			}
		}
		if (bPhoneNumberFound) {
			break;
		}
	}
    
    CFRelease(addressBook);
    CFRelease(allPeople);
	
	sqlite3_bind_text(addStatement, 6, [localFromPhoneId UTF8String], -1, SQLITE_TRANSIENT);
	
	if(SQLITE_DONE != sqlite3_step(addStatement))
		NSAssert1(0, @"Error while inserting data. '%s'", sqlite3_errmsg(database));
	else {
		NSLog(@"TextMessage Record inserted");
	}
	
	sqlite3_reset(addStatement);
	return YES;
}

- (BOOL)getRecentCalls:(NSMutableArray *)arrayList bMissedOnly:(BOOL)bMissedOnly {
	if (!arrayList) {
		return NO;
	}
	
	[arrayList removeAllObjects];
	
	NSString *dbPath = [self getDBPath];
	if (!database) {
		if(sqlite3_open([dbPath UTF8String], &database) != SQLITE_OK) {
			sqlite3_close(database);
			return NO;
		}
	}
	
	if (database) {
        NSString *strQuery = @"SELECT phonenumber, calltime, calltype, duration, localid FROM recentcalls order by id desc";
        if (bMissedOnly) {
            // call type 1 = missed
            strQuery = @"SELECT phonenumber, calltime, calltype, duration, localid FROM recentcalls where calltype = 1 order by id desc";
        }
		const char *cQuery = [strQuery UTF8String];
		sqlite3_stmt *selectStatement;
		
		if (sqlite3_prepare_v2(database, cQuery, -1, &selectStatement, NULL) != SQLITE_OK)
			return NO;
		
		ABAddressBookRef *addressBook = (ABAddressBookRef *) ABAddressBookCreate();
		while (sqlite3_step(selectStatement) == SQLITE_ROW) {
			RecentCallItem *recentItem = [[RecentCallItem alloc] init];
			
			recentItem.PhoneNumber = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectStatement, 0)];
			recentItem.CallTime = sqlite3_column_double(selectStatement, 1);
			recentItem.CallDateTime = [[NSDate dateWithTimeIntervalSince1970:sqlite3_column_double(selectStatement, 1)] description];
			recentItem.CallType = sqlite3_column_int(selectStatement, 2);
			recentItem.Duration = sqlite3_column_int(selectStatement, 3);
			
			recentItem.ExternalId = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectStatement, 4)];
			ABRecordRef person = ABAddressBookGetPersonWithRecordID(addressBook, [recentItem.ExternalId intValue]);
			if (person) {
				recentItem.Name = [ABRecordReader personCompositeName:person];
			} else {
				recentItem.Name = @"";
			}
			
			[arrayList addObject:recentItem];
			
			[recentItem release];
		}
		CFRelease(addressBook);
	}
	
	
	return YES;
}


- (BOOL)addRecentCallItem:(RecentCallItem *)recentCallItem {	
	if (!recentCallItem || !recentCallItem.PhoneNumber) {
		return NO;
	}
	
	NSString *dbPath = [self getDBPath];
	if (!database) {
		if(sqlite3_open([dbPath UTF8String], &database) != SQLITE_OK) {
			sqlite3_close(database);
			return NO;
		}
	}

	sqlite3_stmt *addStatement;
	
	const char *sqlInsert = "insert into recentcalls(phonenumber, calltime, calltype, duration, localid) Values(?, ?, ?, ?, ?)";
	
	if(sqlite3_prepare_v2(database, sqlInsert, -1, &addStatement, NULL) != SQLITE_OK)
		NSAssert1(0, @"Error while creating add statement. '%s'", sqlite3_errmsg(database));
	
	sqlite3_bind_text(addStatement, 1, [recentCallItem.PhoneNumber UTF8String], -1, SQLITE_TRANSIENT);
	sqlite3_bind_double(addStatement, 2, [recentCallItem CallTime]);
	sqlite3_bind_int(addStatement, 3, [recentCallItem CallType]);
	sqlite3_bind_int(addStatement, 4, [recentCallItem Duration]);
	NSString *strLocalId = @"-1";
	NSString *strPhone = recentCallItem.PhoneNumber;
	
	VoxperAppDelegate *app = (VoxperAppDelegate *)[UIApplication sharedApplication].delegate;
	
	if ([strPhone hasPrefix:[app.uiManager.settings getMyLocationCode]]) {
		strPhone = [strPhone substringFromIndex:[[app.uiManager.settings getMyLocationCode] length]];
	}
	
	ABAddressBookRef *addressBook = (ABAddressBookRef *) ABAddressBookCreate();
	
	CFArrayRef allPeople = ABAddressBookCopyArrayOfAllPeople(addressBook);
	CFIndex nPeople = ABAddressBookGetPersonCount(addressBook);
	
	BOOL bPhoneNumberFound = NO;
	for (int i = 0; i < nPeople; i++) {
		ABRecordRef person = CFArrayGetValueAtIndex(allPeople, i);
		if(person != nil){
			NSArray *personPhoneNumbers = [ABRecordReader personContactNumbers:person :YES];
			//NSLog(@"Name: %@ -> , %@", (NSString *)ABRecordCopyValue(person, kABPersonFirstNameProperty), personPhoneNumbers);
			for (int j = 0; j < [personPhoneNumbers count]; j++) {
				NSString *strPhoneNumber = [personPhoneNumbers objectAtIndex:j];
				if ([strPhoneNumber rangeOfString:strPhone].length > 0) {
					strLocalId = [NSString stringWithFormat:@"%d", ABRecordGetRecordID(person)];
					bPhoneNumberFound = YES;
					break;
				}
			}
		}
		if (bPhoneNumberFound) {
			break;
		}
	}
    
	CFRelease(addressBook);
    CFRelease(allPeople);
	
    sqlite3_bind_text(addStatement, 5, [strLocalId UTF8String], -1, SQLITE_TRANSIENT);
	
	if(SQLITE_DONE != sqlite3_step(addStatement))
		NSAssert1(0, @"Error while inserting data. '%s'", sqlite3_errmsg(database));
	else {
		NSLog(@"Recent CallItem Record inserted");
	}
	
	sqlite3_reset(addStatement);
	return YES;
}

- (BOOL)clearAllRecentCalls {
    sqlite3_stmt *deleteStatement;
	
	const char *sqlInsert = "delete from recentcalls";
	
	if(sqlite3_prepare_v2(database, sqlInsert, -1, &deleteStatement, NULL) != SQLITE_OK)
		NSAssert1(0, @"Error while creating add statement. '%s'", sqlite3_errmsg(database));
	
	if(SQLITE_DONE != sqlite3_step(deleteStatement))
		NSAssert1(0, @"Error while inserting data. '%s'", sqlite3_errmsg(database));
	else {
		NSLog(@"Sip Phone number saved. Record inserted");
	}
	
	sqlite3_reset(deleteStatement);
	return YES;
}

- (int)getUnreadMessageCount {
    NSString *dbPath = [self getDBPath];
	if (!database) {
		if(sqlite3_open([dbPath UTF8String], &database) != SQLITE_OK) {
			sqlite3_close(database);
			return NO;
		}
	}
	
	NSString *tempSql = @"select count(read) from messages where read = 0";
	const char *cQuery = [tempSql UTF8String];
	sqlite3_stmt *selectStatement;
	
	if (sqlite3_prepare_v2(database, cQuery, -1, &selectStatement, NULL) != SQLITE_OK)
		return NO;
    
    int nCount = 0;
    
	while (sqlite3_step(selectStatement) == SQLITE_ROW) {
		
		nCount = sqlite3_column_int(selectStatement, 0);
	}
	return nCount;
}

@end
